﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using InformaticaDiary.Common;
using System.Data;

namespace InformaticaDiary.InformaticaDiaryDAL
{
    public class DataAccessSql : IDataAccess
    {
        private readonly string ConnString;
        SqlConnection conn;
        private DataAccessSql()
        {
        }
        public DataAccessSql(string ConnStr)
        {
            if (!String.IsNullOrEmpty(ConnStr))
            {
                ConnString = ConnStr;
            }
            else
            {
                throw new Exception("Connection String cannot be empty");
            }
            try
            {
                conn = new SqlConnection(ConnString);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Common.ExecutedResult Delete(long Id)
        {
            throw new NotImplementedException();
        }

      
        public long Maxid(string strprint)
        {
            throw new NotImplementedException();
        }

        public long Maxid1()
        {
            throw new NotImplementedException();
        }

        public long Maxid2()
        {
            throw new NotImplementedException();
        }

        public Common.ExecutedResult Insert(long Id, Common.CaseDetails objCD)
        {
            throw new NotImplementedException();
        }

        public Common.ExecutedResult Update(long Id, Common.CaseDetails objCD)
        {
            throw new NotImplementedException();
        }

        public Common.ExecutedResult Login(Common.LoginRequest objLoginRequest)
        {
            ExecutedResult obj = new ExecutedResult();

            try
            {
                conn.Open();
                SqlDataReader rdr = null;
                string str = "select USerId, UserName, cisPassword from [CIS].[DairyMaster] where UserName = '" + objLoginRequest.UserName + "' and cisPassword = '" + objLoginRequest.Password + "'";
                SqlCommand cmd = new SqlCommand(str, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                if (rdr.HasRows)
                {
                    string user = rdr["UserName"].ToString();
                    string pwd = rdr["cisPassword"].ToString();
                    if (user.ToLower() == objLoginRequest.UserName.ToLower() & pwd == objLoginRequest.Password)
                    {
                        obj.ErrorNo = 0;
                        obj.ErrorDesc = "";
                    }
                    else
                    {
                        obj.ErrorNo = 9;
                        obj.ErrorDesc = "Invalid UserName or Password.";
                    }
                    //obj.ErrorDesc = "Your Trial version is expired !..............."; to do for
                }
                else
                {
                    obj.ErrorNo = 9;
                    obj.ErrorDesc = "Invalid UserName or Password.";
                }
                rdr.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return obj;
        }


        public List<SearchResult> DoSearch(SearchValue objSearchValue)
        {
            List<SearchResult> objList = new List<SearchResult>();
            try
            {

                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("[CIS].[SearchCaseDetails_1]", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@SearchType", objSearchValue.SearchType));
                cmd.Parameters.Add(new SqlParameter("@UserID", objSearchValue.UserID));
                cmd.Parameters.Add(new SqlParameter("@CaseTypeID", objSearchValue.CaseTypeID));
                cmd.Parameters.Add(new SqlParameter("@CaseYear", objSearchValue.CaseYear));
                cmd.Parameters.Add(new SqlParameter("@TextField", objSearchValue.TextField));

                // execute the command
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    {
                        SearchResult objSearchResult = new SearchResult();
                        objSearchResult.CaseDetailsID = Convert.ToInt64(rdr["CaseDetailsID"]);
                        objSearchResult.sCaseType = Convert.ToString(rdr["CaseTypeName"]);
                        objSearchResult.sCaseNoYear = Convert.ToString(rdr["CaseNoYear"]);
                        objSearchResult.sPetitioner = Convert.ToString(rdr["Petitioner"]);
                        objSearchResult.sRespondant = Convert.ToString(rdr["Respondant"]);
                        objList.Add(objSearchResult);
                    }
                }

            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return objList;
        }

        public List<int> GetYear()
        {
            List<int> objList = new List<int>();
            try
            {
                conn.Open();
                SqlDataReader rdr = null;
                string str;
                str = "SELECT [Year] FROM [CIS].[Year] ORDER BY [Year] DESC";
                SqlCommand cmd = new SqlCommand(str, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        objList.Add(Convert.ToInt32(rdr["Year"]));
                    }
                }

                rdr.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return objList;
        }


        public CaseDetails GetCaseDetails(long CaseDetailsID)
        {
            CaseDetails objCaseDetails = new CaseDetails();
            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("[CIS].[GetCaseDetails]", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@CaseDetailsID", CaseDetailsID));

                // execute the command
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    // iterate through results, printing each to console
                    while (rdr.Read())
                    {
                        objCaseDetails.CaseDetailsID = Convert.ToInt64(rdr["CaseDetailsID"]);
                        objCaseDetails.UserID = Convert.ToInt32(rdr["UserID"]);
                        objCaseDetails.UserName = Convert.ToString(rdr["UserName"]);
                        objCaseDetails.CourtID = Convert.ToInt32(rdr["CourtID"]);
                        objCaseDetails.CourtName = Convert.ToString(rdr["CourtName"]);
                        objCaseDetails.CourtType = Convert.ToString(rdr["CourtType"]);
                        objCaseDetails.CaseTypeId = Convert.ToInt32(rdr["CaseTypeId"]);
                        objCaseDetails.CaseTypeName = Convert.ToString(rdr["CaseTypeName"]);
                        objCaseDetails.CaseYear = Convert.ToInt32(rdr["CaseYear"]);
                        objCaseDetails.StageID = Convert.ToInt32(rdr["StageID"]);
                        objCaseDetails.Stage = Convert.ToString(rdr["Stage"]);
                        objCaseDetails.PoliceStationID = Convert.ToInt32(rdr["PoliceStationID"]);
                        objCaseDetails.PoliceStation = Convert.ToString(rdr["PoliceStation"]);
                        objCaseDetails.Fileno = Convert.ToString(rdr["Fileno"]);
                        objCaseDetails.CaseNo = Convert.ToString(rdr["CaseNo"]);
                        objCaseDetails.BackDate = Convert.ToDateTime(rdr["BackDate"]);
                        objCaseDetails.InstitutionDate = Convert.ToDateTime(rdr["InstitutionDate"]);
                        objCaseDetails.NextDate = Convert.ToDateTime(rdr["NextDate"]);
                        objCaseDetails.Petitioner = Convert.ToString(rdr["Petitioner"]);
                        objCaseDetails.Respondant = Convert.ToString(rdr["Respondant"]);                       
                        objCaseDetails.PetitionerMob = Convert.ToString(rdr["PetitionerMob"]);
                        objCaseDetails.RespondantMob = Convert.ToString(rdr["RespondantMob"]);
                        objCaseDetails.PetitionerDetails = Convert.ToString(rdr["PetitionerDetails"]);
                        objCaseDetails.RespondantDetails = Convert.ToString(rdr["RespondantDetails"]);
                        objCaseDetails.PetitionerAdvocate = Convert.ToString(rdr["PetitionerAdvocate"]);
                        objCaseDetails.RespondantAdvocate = Convert.ToString(rdr["RespondantAdvocate"]);
                        objCaseDetails.FirNo = Convert.ToString(rdr["FirNo"]);
                        objCaseDetails.FirYear = Convert.ToString(rdr["FirYear"]);
                        objCaseDetails.OrderJudgement = Convert.ToString(rdr["OrderJudgement"]);
                        
                    }
                }              
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
            return objCaseDetails;
        }

        public List<CaseType> GetCaseType(long casetypeid = 0)
        {
            List<CaseType> objList = new List<CaseType>();
            try
            {
                conn.Open();
                SqlDataReader rdr = null;
                string str;
                if (casetypeid == 0)
                    str = "SELECT [CaseTypeId], [CaseTypeName] FROM CIS.CaseTypeMaster ORDER BY [CaseTypeName] ASC";
                else
                    str = "select [CaseTypeId], [CaseTypeName] from CIS.CaseTypeMaster where CaseTypeID =" + casetypeid;
                SqlCommand cmd = new SqlCommand(str, conn);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        CaseType objCaseType = new CaseType();
                        objCaseType.CaseTypeID = Convert.ToInt64(rdr["CaseTypeID"]);
                        objCaseType.CaseTypeName = Convert.ToString(rdr["CaseTypeName"]);
                        objList.Add(objCaseType);
                    }
                }

                rdr.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }

            return objList;
        }
    }
}
